#------------------------------------------
# File Name: Code to Make Tables in GLD-IPOR Covid Panel Survey DiB Article
#
#
# Author: Governance and Local Development Institute 
#
# Contact: data@gld.gu.se
#
# Last Updated: March 23, 2022
#
# # Instructions for Use: set working directory
#                       on line 20 to point to 
#                       folder containing 
#                       MCS_Panel_Public.rds
#
#------------------------------------------


#---Set Working Directory
setwd("path_to_file ")


#---Data Import
dat <- readRDS("path_to_file /MCS_Panel_Public.rds")


#---Create Table
#-------------------------------------



library(readxl)
library(Hmisc)
library(lubridate)
library(stringr)
library(plyr)
library(dplyr)
library(labelled)
library(haven)
library(readr)
library(tibble)

dat <- readRDS("path_to_file /MCS_Panel_Public.rds")

# Table 3. Proportion of survey respondents across rounds

freq_table <- table(dat$Surveys)

# Create a table of proportions as percentages
prop_table <- round(prop.table(freq_table) * 100, 2)

# Combine the frequency and proportion tables using add margins()
combined_table <- cbind(freq_table, prop_table)

combined_table <- rownames_to_column(as.data.frame(combined_table), var = "Survey_ Round")

colnames(combined_table) <- c("Survey_Round","Frequency", "Percentage")

# Print the combined table
combined_table



# Table 2 Respondent Characteristics, by Survey Round

                                  #################
                                  # Round ONE
                                  ###################

#For Age_r1
age_groups_r1 <- cut(dat$Age_r1,
                     breaks = c(-Inf,18,26,46,66,Inf),
                     include.lowest = TRUE,
                     right = FALSE,
                     labels = c("DKRA","18-25", "26-45", "46-65", "over 65"))
age_freq_r1 <- table(age_groups_r1)
age_prop_r1 <- prop.table(age_freq_r1)
age_prop_rounded_r1 <- round(age_prop_r1, digits = 3)
age_table_r1 <- cbind(Frequency = age_freq_r1, Percentage = age_prop_rounded_r1)
colnames(age_table_r1) <- c("Frequency", "Percentage")
age_table_r1<- rownames_to_column(as.data.frame(age_table_r1), var = "Age Group")
age_table_r1

# For Gender_r1 variable
gender_freq <- table(dat$Gender_r1)
gender_prop <- prop.table(gender_freq)
gender_prop_rounded <- round(gender_prop, digits = 2)
gender_table <- cbind(Frequency = gender_freq, Percentage =gender_prop_rounded)
colnames(gender_table) <- c("Frequency", "Percentage")
gender_table




# Calculate frequency and proportion of each district
District_freq1 <- table(dat$District_r1)
District_prop1 <- prop.table(District_freq1)
District_prop_rounded1 <- round(District_prop1, digits = 2)
District_table1 <- cbind(Frequency = District_freq1, Percentage = District_prop_rounded1)
colnames(District_table1) <- c("Frequency", "Percentage")
District_table1
# Add District labels in the same row
District_table_with_labels <- rownames_to_column(as.data.frame(District_table1), var = "District Round One")
District_table_with_labels


              
                    ####################
                    #Round Two#
              
                    ##################

#For Age_r1
# Group Age_r2 variable into four age groups
age_groups2 <- cut(dat$Age_r2,
                   breaks = c(18,26,46,66,Inf),
                   include.lowest = TRUE,
                   right = FALSE,
                   labels = c("18-25", "26-45", "46-65", "Over 65"))

# Calculate frequency and proportion of each age group
age_freq <- table(age_groups2)
age_prop <- prop.table(age_freq)
age_prop_rounded <- round(age_prop, digits = 2)
age_table <- cbind(Frequency = age_freq, Percentage = age_prop_rounded)
colnames(age_table) <- c("Frequency", "Percentage")
age_table

# For Gender_r2 variable
gender_freq <- table(dat$Gender_r2)
gender_prop <- prop.table(gender_freq)
gender_prop_rounded <- round(gender_prop, digits = 2)
gender_table <- cbind(Frequency = gender_freq, Percentage =gender_prop_rounded)
colnames(gender_table) <- c("Frequency", "Percentage")
gender_table



# Calculate frequency and proportion of each district
District_freq <- table(dat$District_r2)
District_prop <- prop.table(District_freq)
District_prop_rounded <- round(District_prop, digits = 2)
District_table <- cbind(Frequency = District_freq, Percentage = District_prop_rounded)
colnames(District_table) <- c("Frequency", "Percentage")
District_table
# Add District labels in the same row
District_table_with_labels <- rownames_to_column(as.data.frame(District_table), var = "District Round Two")
District_table_with_labels

print(District_table_with_labels)

                          ###################
                          # Round Three#
                          ###################


# Group Age_r3 variable into four age groups
age_groups <- cut(dat$Age_r3,
                  breaks = c(18,26,46,66,Inf),
                  include.lowest = TRUE,
                  right = FALSE,
                  labels = c("18-25", "26-45", "46-65", "Over 65"))

# Calculate frequency and proportion of each age group
age_freq <- table(age_groups)
age_prop <- prop.table(age_freq)
age_prop_rounded <- round(age_prop, digits = 2)
age_table <- cbind(Frequency = age_freq, Percentage = age_prop_rounded)
colnames(age_table) <- c("Frequency", "Percentage")
age_table

# For Gender_r3 variable
gender_freq <- table(dat$Gender_r3)
gender_prop <- prop.table(gender_freq)
gender_prop_rounded <- round(gender_prop, digits = 2)
gender_table <- cbind(Frequency = gender_freq, Percentage =gender_prop_rounded)
colnames(gender_table) <- c("Frequency", "Percentage")
gender_table



# Calculate frequency and proportion of each district
District_freq <- table(dat$District_r3)
District_prop <- prop.table(District_freq)
District_prop_rounded <- round(District_prop, digits = 2)
District_table <- cbind(Frequency = District_freq, Percentage = District_prop_rounded)
colnames(District_table) <- c("Frequency", "Percentage")
District_table
# Add District labels in the same row
District_table_with_labels <- rownames_to_column(as.data.frame(District_table), var = "District Round three")
District_table_with_labels

print(District_table_with_labels)



##################### Optional ---Proportion of District by survey round #############
# The following code produces the distribution of districts in the survey in a single step
#########################################################################################

# Generate frequency tables and proportions for each variable that meet the conditions
# dat_r1 <- subset(dat, !is.na(SbjNum_r1))
# District_Round_1 <- prop.table(table(dat_r1$District_r1))
# 
# dat_r2 <- subset(dat, !is.na(SbjNum_r2))
# District_Round_2 <- prop.table(table(dat_r2$District_r2))
# 
# dat_r3 <- subset(dat, !is.na(SbjNum_r3))
# District_Round_3 <- prop.table(table(dat_r3$District_r3))
# # Combine frequency tables into a single table
# District_prop_all <- cbind(District_Round_1, District_Round_2, District_Round_3)
# 
# # Round proportions to decimal places
# District_prop_rounded_all <- round(District_prop_all, digits = 2)
# print(District_prop_rounded_all)